import re

import numpy as np
import pandas as pd
from pandas import Series, DataFrame

import PD.PdUtil
from PD import PdUtil


def find_max_number_without_previous(s, nums):
    # 对 s2 进行从大到小排序
    sorted_s2 = nums.sort_values(ascending=False)
    # 只遍历到倒数第二个数，如果没有找到那么返回最后一个数
    for i in range(len(sorted_s2) - 1):
        num = sorted_s2.iloc[i]
        next_num = sorted_s2.iloc[i + 1]
        if num in s.values and next_num not in s.values:
            return num
    return sorted_s2.iloc[-1]


"""
需要列 时延 流量 是否家庭网弱覆盖 楼室层 异常情况（小区一致，ap异常）
新增列 推荐组网类型 实际组网类型 多个账号对应同一个室号
"""


def s1(company):
    delay_col = '平均家庭侧时延(ms)'

    df = pd.read_csv(fr"D:\fttr\{company}全量处理后.csv", dtype={
        '用户账号': str,
        '实际数量': int,
        '单元号': str,
        '层号': str
    })

    def get_value(false_group):

        # 所有类型
        nums = false_group['实际数量'].unique()
        nums = pd.Series(nums)
        res = false_group.groupby('实际数量').filter(lambda group: (group['是否家庭网弱覆盖'] == False).all())[
            '实际数量'].unique()
        res = pd.Series(res)
        rate_sign = false_group['是否家庭网弱覆盖'].mean()
        rate_delay = (false_group[delay_col] > 30).mean()
        no_less = '(不低于)' if (rate_sign == 0) and (rate_delay == 0) else ''
        # 只有一种，判断百分比
        if len(nums) == 1:
            if (rate_sign >= 0.5) or (rate_delay >= 0.5):
                return pd.Series([f'1+{nums[0] + 1}{no_less}', '是'],
                                 index=['推荐组网类型', '最大组合质量仍然为差'])
            else:
                return pd.Series([f'1+{nums[0]}{no_less}', '否' if rate_sign == 0 else '是'],
                                 index=['推荐组网类型', '最大组合质量仍然为差'])
        p = '否'
        if len(res) == 0:
            tt = f'1+{false_group["实际数量"].max() + 1}{no_less}'
            p = '是'
        else:
            # 所有数量类型
            tt = f'1+{find_max_number_without_previous(res, nums)}{no_less}'
        return pd.Series([tt, p], index=['推荐组网类型', '最大组合质量仍然为差'])

    # df['FTTR组网类型'] = '1+' + df["从网关数量"].astype(str)
    df['实际组网类型'] = '1+' + df["实际数量"].astype(str)

    # 筛选 a、b、c 三列同时为空的数据
    condition = df['室号'].isnull() | ((df['地址类型'] == '自然村') & (df[['单元号', '层号']].isnull().any(axis=1)))
    # null_df = df[condition]
    # null_df.to_excel("/temp/非标地址.xlsx")

    df = df[~condition]
    df['室号2'] = df['室号'].str[-2:]
    df['层号'] = df['层号'].fillna('无')
    print(df['层号'])

    df['楼号'] = df['楼号'].fillna('无')
    df['单元号'] = df['单元号'].fillna('无')
    df['室号2'] = df['室号2'].fillna('无')

    # 筛选与crm小区一致的,流量大于1g的
    filter = df[df['小区是否一致'] & (df['天最大总流量(GB)'] > 1) & df['ap正常']]

    result = filter.groupby(['小区ID', '楼号', '单元号', '室号2'])[
        ['是否家庭网弱覆盖', '实际数量', delay_col]].apply(
        get_value).reset_index()
    # 将结果合并回原 DataFrame
    # result = PdUtil.drop_cols(result, '是否家庭网弱覆盖', '平均家庭侧时延(ms)')
    df = pd.merge(df, result, on=['小区ID', '楼号', '单元号', '室号2'], how="left")

    df.loc[~df['小区是否一致'], '推荐组网类型'] = '小区不一致'
    df.loc[~df['ap正常'], '推荐组网类型'] = 'ap异常'
    df['推荐组网类型'] = df['推荐组网类型'].fillna('因低流量原因，无法推荐')

    df.loc[df['天最大总流量(GB)'].fillna(0) < 1, '最大组合质量仍然为差'] = '因低流量原因，无法判断'
    df['最大组合质量仍然为差'] = df['最大组合质量仍然为差'].fillna(df['推荐组网类型'])
    print(df.columns)

    df['是否家庭网弱覆盖'] = df['是否家庭网弱覆盖'].map({
        True: '是',
        False: '否'
    })

    # 计算每个分组的大小
    group_sizes = df.groupby(['小区ID', '楼号', '单元号', '层号', '室号', 'extracted'])['小区ID'].transform('size')

    # 添加标记列
    df['多个账号对应同一个室号'] = ['是' if size > 1 else '否' for size in group_sizes]
    df.to_csv(f'/fttr/{company}-推荐组网.csv', index=False)
    return df


def 筛选最新的账号():
    df = pd.read_csv(r"D:\Download\WeChat Files\wxid_kdchbeq2xllp22\FileStorage\File\2025-04\FTTR模型算法昌平.csv",
                     dtype=str)
    # 将 '时间' 列转换为 datetime 类型
    df['入网时间'] = pd.to_datetime(df['入网时间'])

    # 使用 transform 和 idxmax 找到每个账号对应的最新时间的索引
    idx = df.groupby('用户账号')['入网时间'].transform(max) == df['入网时间']

    # 根据索引筛选出最新时间的行
    result = df[idx]
    PdUtil.to_csv('/temp/FTTR模型算法昌平.csv', result)


def 拆地址(df):
    def t(address):
        if address is None:
            return pd.Series([None, None, None, None])
        # 分别匹配楼、单元、层、室的正则表达式
        building_pattern = r'([a-zA-Z0-9甲乙丙丁]+)(?:号楼|#楼|#|楼|座|栋|幢)'
        building_pattern2 = r'(\d+)号'
        building_pattern3 = r'-([a-zA-Z0-9甲乙丙丁]+)-\d+单元'
        building_pattern4 = r'([东南西北])侧楼'
        unit_pattern = r'([a-zA-Z0-9]+|东|西|南|北)(?:单元|单-)'
        floor_pattern = r'(\d+)层'
        room_pattern = r'([a-zA-Z0-9]+)(?:号)?室'

        unit_match = re.search(unit_pattern, address)
        floor_match = re.search(floor_pattern, address)
        room_match = re.search(room_pattern, address)
        building_match = re.search(building_pattern, address)
        if building_match is None:
            building_match = re.search(building_pattern3, address)
            if building_match is None:
                building_match = re.search(building_pattern4, address)
            if building_match is None:
                building_match = re.search(building_pattern2, address)
                # 判断号是否出现在最后
                if building_match is not None:
                    if building_match.span()[0] == (room_match.span()[0] if room_match else 0):
                        building_match = None

        building = building_match.group(1) if building_match else None
        unit = unit_match.group(1) if unit_match else None
        floor = floor_match.group(1) if floor_match else None
        room = room_match.group(1) if room_match else None
        return pd.Series([building, unit, floor, room])

    df['装机地址'] = df['装机地址'] + '室'
    df[['楼号', '单元号', '层号', '室号']] = df['装机地址'].astype(str).apply(t)
    return df


def 是否弱覆盖(df):
    df['是否家庭网弱覆盖'] = df['是否家庭网弱覆盖'].fillna(df['光猫是否存在WiFi弱覆盖'] == '是')
    return df


def 判断小区是否一致(df):
    t = pd.read_csv(
        r"D:\Download\WeChat Files\wxid_kdchbeq2xllp22\FileStorage\File\2025-04\CRM_-Addr-20250310\crm2.csv",
        dtype=str)
    df = df.merge(t, on='用户账号', how='left')
    df['小区是否一致'] = df['小区ID'] == df['小区编号']
    return df


def 获取所有地址():
    df = pd.read_csv(
        r"D:\Download\WeChat Files\wxid_kdchbeq2xllp22\FileStorage\File\2025-04\20250409105657_ADD_ROOM\20250409105657_ADD_ROOM.csv"
        , usecols=['所属区县', '中文名称'], encoding='gbk')
    df = df[df['所属区县'] == '	昌平区']
    PD.PdUtil.to_csv("/temp/昌平所有地址.csv", df)


def 根据面积调整推荐(df: DataFrame):
    df['推荐数量'] = df['推荐组网类型'].str.extract(r'\+(\d+)').astype(float)
    # 定义区间边界
    bins = [0, 60, 90, 120, 155, 180, float('inf')]
    # 定义区间对应的标签
    labels = [0, 1, 2, 3, 4, 5]

    # 使用 pd.cut 函数根据面积划分区间并新增一列
    df['面积'] = df['面积'].astype(float)
    df['面积数量'] = pd.cut(df['面积'], bins=bins, labels=labels, right=False)
    df['面积数量'] = df['面积数量'].astype('Int64')
    df['推荐数量'] = df['推荐数量'].fillna(-1)
    condition = df['推荐数量'] < df['面积数量']
    df['面积数量'] = "1+" + df['面积数量'].astype(str) + "(平米数)"
    df['面积数量'] = df['面积数量'].mask(df['面积数量'] == '1+5(平米数)', '1+4(特殊/复式别墅户型根据现场实测情况适当增减从设备)')
    df.loc[condition, '推荐组网类型'] = df['面积数量']
    columns = [
        "所属乡镇/街道",
        "所属路/巷/行政村",
        "小区名称",
        "小区ID",
        "所属楼",
        "所属单元",
        "所属层",
        "室",
        "楼",
        "单元",
        "层",
        "楼号",
        "单元号",
        "层号",
        "室号",
        "小区简称",
        "推荐组网类型",
        "实际组网类型",
        '用户账号',
        '是否家庭网弱覆盖',
        '是否已安装宽带', '是否fttr', 'fttr类型', '是否智能组网路由器', '智能组网路由器数', 'fttr路由器数',
        'sa路由器数', '是否需要整改',
        "用途",
        "面积"
    ]
    df = df[columns]
    con = df['是否fttr'].isna()
    df['fttr类型'] = df['fttr类型'].mask(con, np.nan)
    PD.PdUtil.to_csv(f'/fttr/{company}推荐加面积算法.csv', df)


def 额外区分地址字段(df):
    df['小区名称'] = df['小区名称'].str.split('-').str[-1]
    df['小区名称'] = df['小区名称'].astype(str)
    df['装机地址'] = df['装机地址'].astype(str)

    def extract_after_name(row):
        address = row['装机地址']
        name = row['小区名称']
        parts = address.split(name)
        l = len(parts)
        if l > 1:
            return parts[l - 1]
        else:
            return None

    # 使用 apply 方法逐行处理
    df['address'] = df.apply(extract_after_name, axis=1)
    # 定义正则表达式模式
    pattern = r'(.*(?:园|府|阁|院|区))'
    compiled_pattern = re.compile(pattern)

    # 自定义函数
    def extract_first_match(s):
        if pd.notna(s):  # 检查 s 是否为有效字符串
            match = compiled_pattern.search(s)
            if match:
                return next((group for group in match.groups() if group is not None), None)
        return None

    # 应用自定义函数
    df['extracted'] = df['address'].apply(extract_first_match)
    df['extracted'] = df['extracted'].fillna('无')
    return df


def 初始化(company):
    # 判断是否弱覆盖
    df = pd.read_csv(
        fr"D:\Download\WeChat Files\wxid_kdchbeq2xllp22\FileStorage\File\2025-05\{company}结果\{company}结果.csv",
        dtype=str)
    df.rename(columns={
        '账号': '用户账号'
    }, inplace=True)
    del df['分公司']
    df2 = pd.read_csv(r"D:\家宽\综资\综资数据(更新至2025年4月29日)\小区.csv", usecols=['地址类型', '网元内部编码'],
                      encoding='gbk',
                      dtype=str)
    df2.rename(columns={
        "网元内部编码": '小区ID'
    }, inplace=True)

    df3 = pd.read_csv(fr"D:\Download\WeChat Files\wxid_kdchbeq2xllp22\FileStorage\File\2025-05\{company}\{company}.csv",
                      dtype=str)
    df3.rename(columns={
        "宽带账号": '用户账号'
    }, inplace=True)
    df3 = df3[df3['宽带用户状态'] == "在网-正常"]
    df3 = df3.merge(df, on='用户账号', how='left').merge(df2, on='小区ID', how='left')
    PD.PdUtil.to_csv(f"/fttr/{company}全量.csv", df3)


def 平均时延流量(df):
    df2 = pd.read_csv('/fttr/通州大兴时延流量.csv', dtype=str)
    df = df.merge(df2, on='用户账号', how='left')
    return df


def 是否fttr(df: DataFrame):
    df2 = pd.read_csv(r"D:\Download\WeChat Files\wxid_kdchbeq2xllp22\FileStorage\File\2025-04\画像.csv",
                      usecols=["用户账号", "组网1+N弱覆盖", "组网1+0弱覆盖", "从设备离线且存在弱覆盖",
                               "主从过近且弱覆盖", "主网关上行方式"], dtype=str)
    df2 = df2[df2['主网关上行方式'] != 'LAN']
    df2['是否家庭网弱覆盖'] = (df2['组网1+N弱覆盖'] == '是') | (df2['组网1+0弱覆盖'] == '是') | (
            df2['从设备离线且存在弱覆盖'] == '是') | (df2['主从过近且弱覆盖'] == '是')
    df2['是否fttr'] = "是"
    df = df.merge(df2, on="用户账号", how='left')
    return df


def 是否智能组网路由器(df: DataFrame):
    df = PD.PdUtil.drop_cols(df, '是否自装路由器', '家庭网组网类型', '是否智能组网路由器', 'fttr类型')
    df2 = pd.read_csv(r"D:\fttr\实际数量.csv",
                      usecols=['用户账号', '是否自装路由器', '家庭网组网类型', 'fttr类型', '是否智能组网路由器'],
                      dtype=str)
    df = df.merge(df2, on='用户账号', how='left')
    con = df['是否fttr'] == '是'
    df['实际FTTR用户组网类型'] = np.where(con, df['实际组网类型'], '-')
    df['没安装FTTR家庭网组网类型'] = np.where(con, '-', df['家庭网组网类型'])
    df['fttr类型'] = np.where(con, df['家庭网组网类型'], '-')
    return df


def 是否需要整改(df: DataFrame):
    df2 = pd.read_csv("/temp/ap异常账号.csv", dtype=str)
    df['ap正常'] = ~df['用户账号'].isin(df2['宽带账号'])

    df.loc[(df['是否家庭网弱覆盖'] == '是') | (df['平均家庭侧时延(ms)'] > 30), '是否需要整改'] = '是'
    df.loc[~df['小区是否一致'], '是否需要整改'] = '小区不一致'
    df.loc[~df['ap正常'], '是否需要整改'] = 'ap异常'
    df.loc[df['天最大总流量(GB)'].fillna(0) < 1, '是否需要整改'] = '因为低流量原因,暂时无需整改'
    df['是否需要整改'] = df['是否需要整改'].fillna('否')
    # df.loc[(df['是否fttr'] == '是') & (df['是否需要整改'] == '是'), '是FTTR，需要进行整改'] = '是'
    # df.loc[(df['是否fttr'] == '否') & (df['是否需要整改'] == '是'), '不是FTTR，需要进行整改'] = '是'
    return df


def 生成xlsx():
    columns = [
        "用户账号",
        "宽带用户状态",
        "装机地址",
        "小区ID",
        "装机时间",
        "分公司",
        "光猫厂家",
        "小区名称",
        "地址类型",
        "楼号",
        "单元号",
        "层号",
        "室号",
        "智能组网路由器数",
        "fttr路由器数",
        "sa路由器数",
        "平均家庭侧时延(ms)",
        "最大家庭侧时延(ms)",
        "天平均总流量(GB)",
        "天最大总流量(GB)",
        "是否家庭网弱覆盖",
        "最大组合质量仍然为差",
        "多个账号对应同一个室号",
        "是否fttr",
        "是否智能组网路由器",
        "是否自装路由器",
        "实际组网类型",
        "推荐组网类型",
        "fttr类型",
        "是否需要整改"
    ]
    df = pd.read_csv(r"D:\fttr\装机地址-昌平3.csv",
                     dtype=str)
    df = df[columns]
    df.to_excel(r"D:\fttr\装机地址-昌平2.xlsx", index=False)

"""
1.初始化生成全量
2.添加实际组网、异常
3.生成全量处理后
4.生成推荐组网-是否需要整改
5.生成推荐
6、添加面积
7、调整推荐
"""

if __name__ == '__main__':
    company = "大兴"
    # 获取所有地址()
    # 初始化(company)
    根据面积调整推荐(pd.read_csv(f'/fttr/{company}推荐.csv', dtype=str))
    #
    # df = pd.read_csv(fr"D:\fttr\{company}全量.csv", dtype=str)
    # df = 拆地址(df)
    # df = 额外区分地址字段(df)
    # df = 是否fttr(df)
    # df = 是否弱覆盖(df)
    # df = 平均时延流量(df)  # 需要修改路径
    # df = 判断小区是否一致(df)
    # df.to_csv(fr"D:\fttr\{company}全量处理后.csv", index=False)

    # df = s1(company)

    # df = 是否需要整改(df)
    # PD.PdUtil.to_csv(fr"D:\fttr\{company}-推荐组网-是否需要整改.csv", df)
    # # df = pd.read_csv(fr"D:\fttr\{company}-推荐组网-是否需要整改.csv", dtype=str)
    # df.to_excel(fr"D:\fttr\{company}-推荐组网-是否需要整改.xlsx", index=False)
